package com.wyix.service.impl;

import com.wyix.domain.User;
import com.wyix.service.UserService;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Optional;

/**
 * @author wangdx
 * @since 2025/8/20
 */
@Service
public class UserServiceImpl implements UserService {
    private final JdbcTemplate jdbcTemplate;

    public UserServiceImpl(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public List<User> findAll() {
        String sql = "select * from \"user\"";

        return jdbcTemplate.query(sql, new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                return User.builder()
                        .id(rs.getLong("id"))
                        .name(rs.getString("name"))
                        .age(rs.getInt("age"))
                        .email(rs.getString("email"))
                        .build();
            }
        });
    }

    @Override
    public Optional<User> getById(Long id) {
        String sql = "select * from \"user\" where id = ?";
        try {
            User user = jdbcTemplate.queryForObject(sql, new Object[]{id}, (rs, rowNum) ->
                    User.builder()
                            .id(rs.getLong("id"))
                            .name(rs.getString("name"))
                            .age(rs.getInt("age"))
                            .email(rs.getString("email"))
                            .build());
            return Optional.of(user);
        } catch (Exception e) {
            return Optional.empty();
        }
    }

    @Override
    public User create(User user) {
        String sql = "insert into \"user\" (name, age, email) values (?, ?, ?)";

        //创建KeyHolder用于接收生成的自增ID
        KeyHolder keyHolder = new GeneratedKeyHolder();
        //执行插入操作，将生成的自增ID存储到keyHolder中
//        jdbcTemplate.update(sql, new PreparedStatementSetter() {
//            @Override
//            public void setValues(PreparedStatement ps) throws SQLException {
//                ps.setString(1, user.getName());
//                ps.setInt(2, user.getAge());
//                ps.setString(3, user.getEmail());
//            }
//        }, keyHolder);
        jdbcTemplate.update(con -> {
            // 创建 PreparedStatement 时，指定返回自增键（Statement.RETURN_GENERATED_KEYS）
            PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, user.getName());
            ps.setInt(2, user.getAge());
            ps.setString(3, user.getEmail());
            return ps;
        }, keyHolder);

        //从keyHolder中获取生成的自增ID
        Long id = keyHolder.getKey().longValue();
        return new User().builder()
                .id(id)
                .name(user.getName())
                .age(user.getAge())
                .email(user.getEmail())
                .build();

//        jdbcTemplate.update(sql, user.getName(), user.getAge(), user.getEmail());
//        Long id = jdbcTemplate.queryForObject("SELECT LAST_INSERT_ID();", Long.class);
//        return new User().builder()
//                .id(id)
//                .name(user.getName())
//                .age(user.getAge())
//                .email(user.getEmail())
//                .build();
    }

    @Override
    public boolean update(Long id, User user) {
        String sql = "update \"user\" set name = ?, age = ?, email = ? where id = ?";
        int updated = jdbcTemplate.update(sql, user.getName(), user.getAge(), user.getEmail(), id);
        return updated > 0;
    }

    @Override
    public boolean deleteById(Long id) {
        String sql = "delete from \"user\" where id = ?";
        int deleted = jdbcTemplate.update(sql, id);
        return deleted > 0;
    }
}
